package dao;
import entity.UserInfoEntity;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SAMDao {
    // 数据库连接相关信息
    private static final String DB_URL = "jdbc:mysql://localhost:3306/house_manage";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "123456";

    // 建立数据库连接的方法
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
    }

    // 关闭数据库连接的方法
    private void closeConnection(Connection conn, Statement stmt, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 数据库增删改查的方法示例
    public void insertData(String data) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = getConnection();
            String sql = "INSERT INTO mytable (column_name) VALUES (?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, data);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn, stmt, null);
        }
    }

    public void updateData(int id, String newData) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = getConnection();
            String sql = "UPDATE mytable SET column_name = ? WHERE id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, newData);
            stmt.setInt(2, id);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn, stmt, null);
        }
    }

    public void deleteData(int id) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = getConnection();
            String sql = "DELETE FROM mytable WHERE id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, id);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn, stmt, null);
        }
    }

    public UserInfoEntity fetchData(String userName,String password) throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<UserInfoEntity> users = new ArrayList<>();
        try {
            conn = getConnection();
            String sql = "SELECT * FROM userinfo WHERE userName=? AND userPass=?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, userName);
            stmt.setString(2, password);
            rs = stmt.executeQuery();
            // 处理查询结果
            while (rs.next()) {
                UserInfoEntity user = new UserInfoEntity();
                user.setId(rs.getString("id"));
                user.setUserName(rs.getString("userName"));
                user.setPassword(rs.getString("userPass"));
                user.setRealName(rs.getString("realName"));
                user.setSex(rs.getString("sex"));
                user.setPhone(rs.getString("phone"));
                user.setAddressId(rs.getInt("addressId"));
                user.setBeizhu(rs.getString("beizhu"));
                user.setNickName(rs.getString("nickName"));
                user.setCreateTime(rs.getString("createTime"));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn, stmt, rs);
        }

        if(users.size()>1){
            throw new Exception("用户不止一个");
        }
        if (users.size()==1){
            return users.get(0);
        }
        else {
            return null;
        }
    }
}